<?php
class User_model extends CI_Model {
    function __construct() {
        parent::__construct();
        $this->load->database();  
        $this->load->helper(array('form', 'url'));
        $this->load->library('form_validation');
        $this->load->library('session');
    }
        
    function check($email)//check for signup
    {
        $query=$this->db->get("user");
        foreach ($query->result() as $row)
        {
            if($row->EMAIL == $email)
            {
               return TRUE; 
            }
        }
        return FALSE;
    }
    
    function checklogin($email, $password) // check xem user de login
    {
        $sha1pass = sha1($password); //md5($password);
        $query=$this->db->get("user");


        //echo "mail cua ban la ".$email."</br>";
        //echo "mk la ".md5($password)."</br>";
        foreach ($query->result() as $row)
        {
            //echo "mail trong csdl la ".$row->EMAIL."</br>";
            //echo "pass trong csdl la ".$row->PASSWORD."</br>";
            if($row->EMAIL == $email && $row->PASSWORD == $sha1pass)
            {
               return TRUE; 
            }
        }
        return FALSE;
    }   
    
    //Kiem tra phan dang nhap cua tai khoan giao vien phu trach
    function checklogin_incharge_teacher($email, $password) // check xem user de login
    {
        $sha1pass = sha1($password); //md5($password);
        $query=$this->db->get("teacher");


        //echo "mail cua ban la ".$email."</br>";
        //echo "mk la ".md5($password)."</br>";
        $user['user'] = null;
        foreach ($query->result() as $row)
        {
            //echo "mail trong csdl la ".$row->EMAIL."</br>";
            //echo "pass trong csdl la ".$row->PASSWORD."</br>";
            if($row->EMAIL == $email && $row->PASSWORD == $sha1pass)
            {
                $user['user']['SQL_ORDER'] = $row->SQL_ORDER;
                $user['user']['ID'] = $row->ID;
                $user['user']['USERNAME'] = $row->NICK_NAME;
                $user['user']['EMAIL'] = $row->EMAIL;
                $user['user']['FULLNAME'] = $row->FULLNAME;
                $user['user']['gender'] = $row->GENDER;
                $user['user']['phone'] = $row->PHONE;
                //$user['user']['ans_quan'] = $row->ANS_QUAN;
                //$user['user']['ques_quan'] = $row->QUES_QUAN;
                $user['user']['public_allowed_inf'] = $row->PUBLIC_ALLOWED_INF;
                $user['user']['user_data'] = "incharge_teacher";
                $user['user']['validated'] = true;
                //$user['user']['reg_id_of_courses'] = $row->USER_ID_OF_COURSES;
                //$user['user']['reg_course_quan'] = $row->COURSE_QUAN;
                //$user['user']['unreg_id_of_courses'] = $row->UNREGISTED_ID_OF_COURSES;
                //$user['user']['unreg_course_quan'] = $row->UNREGISTED_COURSE_QUAN;
                $user['user']['school_level'] = $row->SCHOOL_LEVEL;
                $user['user']['school_name'] = $row->SCHOOL_NAME;
                $user['user']['create_date'] = $row->CREATE_DATE;
                $user['user']['birthday'] = $row->BIRTHDAY; 
                $this->session->set_userdata($user['user']);  
                return $user; 
            }
        }
        $user['user']['validated'] = false;
        $this->session->set_userdata($user['user']);
        
        return $user;
    }   
    
            
    function check_pass ($id_user, $pass) {
        $sha1pass = sha1($pass);
        $sql = "SELECT *
                FROM user
                WHERE ID = $id_user";
        $take_query = $this->db->query($sql);
        foreach ($take_query->result() as $row) {
            if ($sha1pass == $row->PASSWORD) {
                return TRUE;
            }
        }
        return FALSE;
        
    }
    
    function reset_pass ($id_user, $new_pass){
        $sha1pass = sha1($new_pass);
        $sql = "UPDATE user
                SET PASSWORD = '$sha1pass'
                WHERE ID = $id_user";
        $this->db->query($sql);
    }
    
    function update_allow_inf($id_user, $allow_inf, $type_of_user) {
        //echo $allow_inf;
        switch($type_of_user) {
            case "normal_user":
                $sql = "UPDATE user
                        SET PUBLIC_ALLOWED_INF = $allow_inf 
                        WHERE ID = $id_user";
                break;
            case "in_charge_teacher":
                $sql = "UPDATE teacher
                        SET PUBLIC_ALLOWED_INF = $allow_inf 
                        WHERE SQL_ORDER = $id_user";
                break;
                break;
        }
        $this->db->query($sql);
    }
    
    function get_data_general_course(){
        $query = $this->db->get("general_data");
        foreach ($query->result() as $row) {
            $get_data['course_quan'] = $row->course_quan_valiable;
            $get_data['id_of_courses'] = $row->id_of_courses_valiable;
        }
        return $get_data;
    }
    
    function insert_new_user($data)
    {
        //var_dump($data);
        $this->db->insert("user", $data);
        $sql = "SELECT user_quan 
                FROM general_data";
        $query = $this->db->query($sql);
        $i = 0;
        foreach($query->result() as $row) {
            $i = $row->user_quan + 1;
            
        }
        $sql="UPDATE general_data
                SET user_quan = $i";
        $this->db->query($sql); 
    }
    
    public function view_user($email)
    {
        //$this->load->view("frontend/view_user_login");
        //echo $email;
        $query = $this->db->get("user");
        foreach ($query->result() as $row)
        {
            if($row->EMAIL == $email)
            {
                //var_dump($row);
                $user['user']['ID'] = $row->ID;
                $user['user']['USERNAME'] = $row->NAME;
                $user['user']['EMAIL'] = $row->EMAIL;
                $user['user']['FULLNAME'] = $row->FULLNAME;
                $user['user']['gender'] = $row->GENDER;
                $user['user']['ans_quan'] = $row->ANS_QUAN;
                $user['user']['ques_quan'] = $row->QUES_QUAN;
                $user['user']['public_allowed_inf'] = $row->PUBLIC_ALLOWED_INF;
                $user['user']['user_data'] = "user";
                $user['user']['validated'] = true;
                $user['user']['reg_id_of_courses'] = $row->USER_ID_OF_COURSES;
                $user['user']['reg_course_quan'] = $row->COURSE_QUAN;
                $user['user']['unreg_id_of_courses'] = $row->UNREGISTED_ID_OF_COURSES;
                $user['user']['unreg_course_quan'] = $row->UNREGISTED_COURSE_QUAN;
                $user['user']['school_level'] = $row->SCHOOL_LEVEL;
                $user['user']['school'] = $row->SCHOOL;
                $user['user']['create_date'] = $row->CREATE_DATE;
                $user['user']['year_of_birth'] = $row->YEAR_OF_BIRTH;
                
                //echo $row->USERNAME;
            }
        }
        $this->session->set_userdata($user['user']);
        return $user;
    }
    
    function get_courses_by_id($id_courses, $unreg_id_courses) {
        $sql = "SELECT * 
                FROM course 
                ORDER BY course_id  ASC";
        $take_query = $this->db->query($sql);
        
        $i = 0;
        $j = 0;
        foreach ($take_query->result() as $row) {
            //echo $row->course_name;
            if (isset($id_courses[$i])&&($row->course_id == $id_courses[$i])) {
                $get_data['reg'][$i]['course_id'] = $row->course_id;
                $get_data['reg'][$i]['course_code'] = $row->course_code;
                $get_data['reg'][$i]['course_open_day'] = $row->course_open_day;
                $get_data['reg'][$i]['course_main_lecturer'] = $row->course_main_lecturer;
                $get_data['reg'][$i]['course_lecture_quan'] = $row->course_lecture_quan;
                $get_data['reg'][$i]['course_level_release'] = $row->course_level_release;
                $get_data['reg'][$i]['course_id_of_lectures'] = $row->course_id_of_lectures;
                $get_data['reg'][$i]['course_img_path'] = $row->course_img_path;
                $get_data['reg'][$i]['course_txt_introduce'] = $row->course_txt_introduce;
                $get_data['reg'][$i]['course_quan_users'] = $row->course_quan_users;
                $get_data['reg'][$i]['course_lecturer_quan'] = $row->course_lecturer_quan;
                $get_data['reg'][$i]['course_id_of_lecturer'] = $row->course_id_of_lecturer;
                $get_data['reg'][$i]['course_name'] = $row->course_name;
                $get_data['course_name'][$id_courses[$i]] = $row->course_name;
                //echo $get_data[$i]['course_name'];
                $i++;                
            } else {
                if (isset($unreg_id_courses[$j])&&($row->course_id == $unreg_id_courses[$j])) {
                    $get_data['unreg'][$j]['course_id'] = $row->course_id;
                    $get_data['unreg'][$j]['course_code'] = $row->course_code;
                    $get_data['unreg'][$j]['course_open_day'] = $row->course_open_day;
                    $get_data['unreg'][$j]['course_main_lecturer'] = $row->course_main_lecturer;
                    $get_data['unreg'][$j]['course_lecture_quan'] = $row->course_lecture_quan;
                    $get_data['unreg'][$j]['course_level_release'] = $row->course_level_release;
                    $get_data['unreg'][$j]['course_id_of_lectures'] = $row->course_id_of_lectures;
                    $get_data['unreg'][$j]['course_img_path'] = $row->course_img_path;
                    $get_data['unreg'][$j]['course_txt_introduce'] = $row->course_txt_introduce;
                    $get_data['unreg'][$j]['course_quan_users'] = $row->course_quan_users;
                    $get_data['unreg'][$j]['course_lecturer_quan'] = $row->course_lecturer_quan;
                    $get_data['unreg'][$j]['course_id_of_lecturer'] = $row->course_id_of_lecturer;
                    $get_data['unreg'][$j]['course_name'] = $row->course_name;
                    $get_data['course_name'][$unreg_id_courses[$j]] = $row->course_name;
                    //echo $get_data[$i]['course_name'];
                    $j++;                
                }
            }          
            
        }
        return $get_data;
    }
    
    public function get_single_course_by_id($id_course) {
        $sql = "SELECT * 
                FROM course 
                WHERE course_id = $id_course";
        $take_query = $this->db->query($sql);
        
        foreach( $take_query->result() as $row) {
            $get_data['course_id'] = $row->course_id;
            $get_data['course_code'] = $row->course_code;
            $get_data['course_open_day'] = $row->course_open_day;
            $get_data['course_main_lecturer'] = $row->course_main_lecturer;
            $get_data['course_lecture_quan'] = $row->course_lecture_quan;
            $get_data['course_level_release'] = $row->course_level_release;
            $get_data['course_id_of_lectures'] = $row->course_id_of_lectures;
            $get_data['course_img_path'] = $row->course_img_path;
            $get_data['course_txt_introduce'] = $row->course_txt_introduce;
            $get_data['course_quan_users'] = $row->course_quan_users;
            $get_data['course_lecturer_quan'] = $row->course_lecturer_quan;
            $get_data['course_id_of_lecturer'] = $row->course_id_of_lecturer;
            $get_data['course_name'] = $row->course_name;
        }

        return $get_data;
    }
    
    
    //When user signup a course
    public function update_course($id_user, $email_user, $reg_courses, $unreg_courses, $reg_quan, $unreg_quan, $course_signup_code) {
        //Update the registered and unregistered fields in row corepond to current user in  user table
        $sql = "UPDATE user
                SET USER_ID_OF_COURSES = '$reg_courses',
                    UNREGISTED_ID_OF_COURSES = '$unreg_courses',
                    COURSE_QUAN = '$reg_quan',
                    UNREGISTED_COURSE_QUAN =  '$unreg_quan'
                WHERE ID = $id_user"; 
        $this->db->query($sql);
        
        //Get the email of excercise incharger
        $sql = "SELECT QUAN_INCHARGE_EXCERCISE, EMAIL_INCHARGE_EXCERCISE, FLAG_INCHARGE_EXCERCISE 
                FROM course
                WHERE course_code = '$course_signup_code'";              
        $query = $this->db->query($sql);  
        foreach ($query->result() as $row) {
            $quan_incharge_excercise = $row->QUAN_INCHARGE_EXCERCISE;
            $email_incharge_excercise = $row->EMAIL_INCHARGE_EXCERCISE;
            $flag_incharge_excercise = $row->FLAG_INCHARGE_EXCERCISE;
        }
        $i = 0;
        $get_email_incharge_excercise = "0000";
        $j = 0;
        $c = '';
        while ($i < $flag_incharge_excercise) {
            $c = $email_incharge_excercise[$j];
            if ($c == '|') {
                $i++;
            }
            $j++;
        }
        $i = 0;
        while ($email_incharge_excercise[$j] != '|') {
            $get_email_incharge_excercise[$i] = $email_incharge_excercise[$j];
            $i++;
            $j++;     
        }
        
        //Update the flag of excercise incharger in course table
        $flag_incharge_excercise = (($flag_incharge_excercise+1) % $quan_incharge_excercise);
        $sql = "UPDATE course
                SET FLAG_INCHARGE_EXCERCISE = $flag_incharge_excercise
                WHERE course_code = '$course_signup_code'";
        $this->db->query($sql);
        
        //Add an row corresponding to current user and signup course to grade table corresponding to the signup course
        $course_signup_code_lower = "00"; //Inital the string
        //get the name of grade database table coressponding the signup course 
        $i = 0;
        $j = 0;
        while (isset($course_signup_code[$i])) {
            if ($course_signup_code[$i] == '.') {
                $course_signup_code_lower[$j] = 'd';
                $course_signup_code_lower[$j+1] = 'o';
                $course_signup_code_lower[$j+2] = 't';
                $j = $j + 3;
            } else {
                $course_signup_code_lower[$j] = strtolower($course_signup_code[$i]);
                $j++;
            }
            $i++;
        }
        $name_table = $course_signup_code_lower."_grade";
        $sql = "INSERT INTO $name_table(USER_ROW_ORDER, EMAIL_PUPIL, EMAIL_INCHARGE_EXCERCISE)
                            VALUES ($id_user, '$email_user', '$get_email_incharge_excercise')";
        $this->db->query($sql);
        
    }
    
    function get_in_charge_excercise_email($user_row_order, $course_code) {
        $course_code_lower = "00"; //Inital the string
        //get the name of grade database table coressponding the signup course 
        $i = 0;
        $j = 0;
        while (isset($course_code[$i])) {
            if ($course_code[$i] == '.') {
                $course_code_lower[$j] = 'd';
                $course_code_lower[$j+1] = 'o';
                $course_code_lower[$j+2] = 't';
                $j = $j + 3;
            } else {
                $course_code_lower[$j] = strtolower($course_code[$i]);
                $j++;
            }
            $i++;
        }
        $name_table = $course_code_lower."_grade";
        $sql = "SELECT EMAIL_INCHARGE_EXCERCISE
                FROM $name_table
                WHERE USER_ROW_ORDER = $user_row_order";
        $query = $this->db->query($sql);
        foreach ($query->result() as $row) {
            $email_incharge_excercise = $row->EMAIL_INCHARGE_EXCERCISE;
        }
        return $email_incharge_excercise;
    }
    
    function insert_fail_mail_excercise ($course_code, $email_user, $path, $email_incharge_excercise) {
        $sql = "INSERT INTO fail_mail_excercise (COURSE_CODE, EMAIL_USER, PATH, EMAIL_INCHARGE_EXCERCISE)
                VALUES ('$course_code', '$email_user', '$path', '$email_incharge_excercise')";
        $this->db->query($sql);
    }
    
    
    function change_school_name($id, $new_school_name) {
        $sql = "UPDATE user
                SET SCHOOL = '$new_school_name'
                WHERE ID = $id";
        echo $id." ".$new_school_name;
        $this->db->query($sql);
        //$this->load->library('session');
        //$this->session->userdata['school'] = $new_school_name;
        
    }
    
    function update_course_for_who_cant_take_third_course() {
        $sql = "UPDATE user
                SET USER_ID_OF_COURSES = '001002003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000',
                    UNREGISTED_ID_OF_COURSES = '000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000',
                    COURSE_QUAN = '3',
                    UNREGISTED_COURSE_QUAN =  '0'
                WHERE USER_ID_OF_COURSES = '001002000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'"; 
        $this->db->query($sql);
    }
    

    function make_pdf_file_for_user_table() {
        $query = $this->db->get("user");
        $i = 0;
        foreach ($query->result() as $row)
        {
                //var_dump($row);
                $user[$i]['ID'] = $row->ID;
                $user[$i]['USERNAME'] = $row->NAME;
                $user[$i]['EMAIL'] = $row->EMAIL;
                $user[$i]['FULLNAME'] = $row->FULLNAME;
                $user[$i]['gender'] = $row->GENDER;
                $user[$i]['ans_quan'] = $row->ANS_QUAN;
                $user[$i]['ques_quan'] = $row->QUES_QUAN;
                $user[$i]['public_allowed_inf'] = $row->PUBLIC_ALLOWED_INF;
                $user[$i]['user_data'] = "user";
                $user[$i]['validated'] = true;
                $user[$i]['reg_id_of_courses'] = $row->USER_ID_OF_COURSES;
                $user[$i]['reg_course_quan'] = $row->COURSE_QUAN;
                $user[$i]['unreg_id_of_courses'] = $row->UNREGISTED_ID_OF_COURSES;
                $user[$i]['unreg_course_quan'] = $row->UNREGISTED_COURSE_QUAN;
                $user[$i]['school_level'] = $row->SCHOOL_LEVEL;
                $user[$i]['school'] = $row->SCHOOL;
                $user[$i]['create_date'] = $row->CREATE_DATE;
                $user[$i]['year_of_birth'] = $row->YEAR_OF_BIRTH;
                $i++;
        }
        $user['quan'] = $i;
        return $user;
    }
    
    function update_all_grade() {
        $sql = "SELECT ID
                FROM user
                WHERE (ID > 905)";
        $query = $this->db->query($sql);
        
        foreach ($query->result() as $row) {
            
        $user_id = $row->ID;  
          
        //EGO.C1
        //Get the email of excercise incharger
        $sql = "SELECT QUAN_INCHARGE_EXCERCISE, EMAIL_INCHARGE_EXCERCISE, FLAG_INCHARGE_EXCERCISE 
                FROM course
                WHERE course_code = 'EGO.G1'";              
        $query = $this->db->query($sql);  
        foreach ($query->result() as $row) {
            $quan_incharge_excercise = $row->QUAN_INCHARGE_EXCERCISE;
            $email_incharge_excercise = $row->EMAIL_INCHARGE_EXCERCISE;
            $flag_incharge_excercise = $row->FLAG_INCHARGE_EXCERCISE;
        }
        $i = 0;
        $get_email_incharge_excercise = "0000";
        $j = 0;
        $c = '';
        while ($i < $flag_incharge_excercise) {
            $c = $email_incharge_excercise[$j];
            if ($c == '|') {
                $i++;
            }
            $j++;
        }
        $i = 0;
        while ($email_incharge_excercise[$j] != '|') {
            $get_email_incharge_excercise[$i] = $email_incharge_excercise[$j];
            $i++;
            $j++;     
        }
        
        //Update the flag of excercise incharger in course table
        $flag_incharge_excercise = (($flag_incharge_excercise+1) % $quan_incharge_excercise);
        $sql = "UPDATE course
                SET FLAG_INCHARGE_EXCERCISE = $flag_incharge_excercise
                WHERE course_code = 'EGO.G1'";
        $this->db->query($sql);
        
        //Add an row corresponding to current user and signup course to grade table corresponding to the signup course

        $sql = "INSERT INTO egodotg1_grade(USER_ROW_ORDER, EMAIL_INCHARGE_EXCERCISE)
                            VALUES ($user_id, '$get_email_incharge_excercise')";
        $this->db->query($sql);
        

        
        }
        
    }
    
    function update_email_grade() {
        $sql = "UPDATE egodotg1_grade
                SET EMAIL_INCHARGE_EXCERCISE = 'kien8altt@gmail.com'
                WHERE EMAIL_INCHARGE_EXCERCISE = 'kien9altt@yahoo.com'";
        $this->db->query($sql);
    }
    
    
}
    